﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.OleDb;
using System.IO;

namespace FileOperationWinform
{
    public class OperaExcel
    {
        public static string ExportTable2ExcelFile(DataTable dt, string ExcelFileName, string strWorkSheetName)
        {
            if (File.Exists(ExcelFileName) == false)
            {
                return "指定文件不存在！";
            }

            if (dt == null)
            {
                return "数据不能为空！";
            }

            if (strWorkSheetName.ToString() == "")
            {
                return "数据表名不可以为空！";
            }
            dt.TableName = strWorkSheetName;

            int iRows = dt.Rows.Count;
            int iCols = dt.Columns.Count;

            StringBuilder stringBuilder;
            string connString;

            if (iRows == 0)
            {
                return "没有可导入数据！";
            }

            stringBuilder = new StringBuilder();


            connString = OperaExcel.GetExcelConnection(ExcelFileName);// "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFileName + ";Extended Properties=Excel 8.0;";

            //先查看此Excel中是否有相关Table，如果有的话就删除，然后导入新的。 
            //生成创建表的脚本 
            stringBuilder.Append("CREATE TABLE ");
            stringBuilder.Append(dt.TableName + " ( ");
            for (int i = 0; i < iCols; i++)
            {
                //此处是本版本改进中最实用的地方 
                string strType = OperaExcel.GetOleDataType(dt.Columns[i]);
                if (i < iCols - 1)
                    stringBuilder.Append(string.Format("{0} {1},", dt.Columns[i].ColumnName, strType));
                else
                    stringBuilder.Append(string.Format("{0} {1})", dt.Columns[i].ColumnName, strType));
            }


            using (OleDbConnection objConn = new OleDbConnection(connString))
            {


                OleDbCommand objCmd = new OleDbCommand();
                objCmd.Connection = objConn;

                //插入新表 
                objCmd.CommandText = stringBuilder.ToString();

                try
                {
                    objConn.Open();
                    //插入新表 
                    objCmd.ExecuteNonQuery();

                }
                catch (Exception e)
                {
                    return "在Excel中创建表失败！错误信息：" + e.Message;
                }

                stringBuilder.Remove(0, stringBuilder.Length);

                stringBuilder.Append("INSERT INTO ");
                stringBuilder.Append(dt.TableName + " ( ");

                //先插入标头 
                for (int i = 0; i < iCols; i++)
                {
                    if (i < iCols - 1)
                        stringBuilder.Append(dt.Columns[i].ColumnName + ",");
                    else
                        stringBuilder.Append(dt.Columns[i].ColumnName + ") values (");
                }

                for (int i = 0; i < iCols; i++)
                {

                    if (i < iCols - 1)
                        stringBuilder.Append("@" + dt.Columns[i].ColumnName + ",");
                    else
                        stringBuilder.Append("@" + dt.Columns[i].ColumnName + ")");
                }


                //建立插入动作的Command 
                objCmd.CommandText = stringBuilder.ToString();
                OleDbParameterCollection oleParam = objCmd.Parameters;

                oleParam.Clear();
                for (int i = 0; i < iCols; i++)
                {
                    OleDbType oleDbType = OperaExcel.GetRefOleDataType(dt.Columns[i]);

                    //此处是本版本改进中最实用的地方 
                    oleParam.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, oleDbType));
                }


                //遍历DataTable将数据插入新建的Excel文件中 
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < oleParam.Count; i++)
                    {
                        oleParam[i].Value = row[i];
                    }

                    objCmd.ExecuteNonQuery();


                }
                return "数据已成功导入Excel！";
            }
        }

        /// <summary> 
        /// 获取与本地DataSet中指定列的类型对应的OleDbType的数据类型字符串 
        /// </summary> 
        /// <param name="dataColumn"></param> 
        /// <returns></returns> 
        public static string GetOleDataType(DataColumn dataColumn)
        {
            switch (dataColumn.DataType.Name)
            {
                case "String"://字符串 
                    {
                        return "VarChar";
                    }
                case "Double"://数字 
                    {
                        return "Double";
                    }
                case "Decimal"://数字 
                    {
                        return "Decimal";
                    }
                case "DateTime"://时间 
                    {
                        return "Date";
                    }
                default:// 
                    {
                        return "VarChar";
                    }
            }
        }

        /// <summary> 
        /// 获取与本地DataSet中指定列的类型对应的OleDbType类型 
        /// </summary> 
        /// <param name="dataColumn"></param> 
        /// <returns></returns> 
        public static OleDbType GetRefOleDataType(DataColumn dataColumn)
        {
            switch (dataColumn.DataType.Name)
            {
                case "String"://字符串 
                    {
                        return OleDbType.VarChar;
                    }
                case "Double"://数字 
                    {
                        return OleDbType.Double;
                    }
                case "Decimal"://数字 
                    {
                        return OleDbType.Decimal;
                    }
                case "DateTime"://时间 
                    {
                        return OleDbType.Date;
                    }
                default:
                    {
                        return OleDbType.VarChar;
                    }
            }
        }
        //public static string GetExcelConnection(string strFilePath)
        //{
        //    if (!File.Exists(strFilePath))
        //    {
        //        throw new Exception("指定的Excel文件不存在！");
        //    }
        //    return
        //         @"Provider=Microsoft.Jet.OLEDB.4.0;" +
        //         @"Data Source=" + strFilePath + ";" +
        //         @"Extended Properties=" + Convert.ToChar(34).ToString() +
        //         @"Excel 8.0;" + "Imex=2;HDR=Yes;" + Convert.ToChar(34).ToString();
        //}
        //public static string GetExcelConnection(string strFilePath)
        //{
        //    if (!File.Exists(strFilePath))
        //    {
        //        throw new Exception("指定的Excel文件不存在！");
        //    }
        //    return
        //         @"Provider=Microsoft.ace.oledb.12.0;" +
        //         @"Data Source=" + strFilePath + ";" +
        //         @"Extended Properties="+
        //         @"'Excel 12.0;" + "HDR=Yes;'" ;
        //}
        public static string GetExcelConnection(string strFilePath)
        {
            if (!File.Exists(strFilePath))
            {
                throw new Exception("指定的Excel文件不存在！");
            }
            return
                 @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                 @"Data Source=" + strFilePath + ";" +
                 @"Extended Properties=" +
                 @"'Excel 8.0;" + "HDR=Yes;'";
        }


    }
}
